package com.hnkywl.sts.dao.cwgl.kmys;

import com.hnkywl.sts.entity.cwgl.kmys.Ljkmdmys;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-5-11
 * Time: 上午12:54
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class LjkmdmysDao extends SimpleHibernateTemplate<Ljkmdmys, Long> {
    //原科目代码映射页面导入调用方法
    public Page<Map> findAll(Long nbgsId, Long qyjgId, String kmdm, int pageNum, int pageSize) {
        Page<Map> page = new Page<Map>(pageNum, pageSize, 0);
        String count = "select count(1) ";
        String query = "select * ";
        StringBuilder where = new StringBuilder();
        if (nbgsId != null) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("lj.nbgs = :nbgsId");
        }
        if (qyjgId != null) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("lj.qyjg = :qyjgId");
        }
        if (StringUtils.isNotEmpty(kmdm)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("ljkhdmys.kmdm like '%" + kmdm + "%'");
        }
        String from = "from (select ljkhdmys.id as \"ljkmdmys.id\",lj.nbgs as \"ljkmdmys.nbgsId\",lj.nbgsqc as \"nbgsQc\", " +
                "  lj.qyjg as \"ljkmdmys.qyjgId\",lj.qyjgqc as \"qyjgQc\",lj.lx as \"ljkmdmys.lx\",ljkhdmys.kmdm as \"ljkmdmys.kmdm\", " +
                "  row_number() over(partition by lj.nbgs, lj.qyjg, lj.lx order by lj.nbgs, lj.qyjg, lj.lx asc) rd " +
                "  from t_sys_ljkhdmys ljkhdmys" +
                " right outer join (select gsjg.id nbgs,gsjg.qc nbgsqc,qyjg.id qyjg,qyjg.qc qyjgqc, 0 lx " +
                "    from t_lj_sy sy " +
                "    left join t_sys_lj lj on (sy.lj_id = lj.id) " +
                "    left join t_sys_gsjg gsjg on (lj.jyjg_id = gsjg.id) " +
                "    left join t_sys_gsjg qyjg on (sy.sy_id = qyjg.id and qyjg.type = 'qyjg') " +
                "   where lj.deleted = 0 " +
                "  union all " +
                "  select gsjg.id nbgs,gsjg.qc nbgsqc,qyjg.id qyjg,qyjg.qc qyjgqc,1 lx " +
                "    from t_lj_xy xy " +
                "    left join t_sys_lj lj on (xy.lj_id = lj.id) " +
                "    left join t_sys_gsjg gsjg on (lj.jyjg_id = gsjg.id) " +
                "    left join t_sys_gsjg qyjg on (xy.xy_id = qyjg.id and qyjg.type = 'qyjg') " +
                "   where lj.deleted = 0 " +
                "  union all " +
                "  select a.id as nbgs,a.qc as nbgsqc,b.id as qyjg,b.qc as qyjgqc,0 as lx " +
                "    from t_sys_gsjg a, t_sys_gsjg b " +
                "   where a.id = 1 and a.type = 'nbgs' and a.sjjg_id is null and a.deleted = 0 and a.sfzx = 0" + //todo 公司机构表的供应商Id未加
                "     and b.type = 'qyjg' and b.bggys_id is not null and a.deleted = 0 and a.sfzx = 0 ) lj on (lj.nbgs = ljkhdmys.nbgsid and lj.qyjg = ljkhdmys.qyjgid and lj.lx = ljkhdmys.lx) " +
                (where.length() != 0 ? " where " : "") + where +
                " order by lj.nbgs, lj.qyjg, lj.lx asc) where rd = 1";
        SQLQuery sqlQuery = getSession().createSQLQuery(query + from);
        SQLQuery countQuery = getSession().createSQLQuery(count + from);
        if (nbgsId != null) {
            sqlQuery.setParameter("nbgsId", nbgsId);
            countQuery.setParameter("nbgsId", nbgsId);
        }
        if (qyjgId != null) {
            sqlQuery.setParameter("qyjgId", qyjgId);
            countQuery.setParameter("qyjgId", qyjgId);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        return page;
    }


    //新科目代码映射页面导入调用方法
    public Page<Map> findAlls(String qyjgQc, String gysfzhsdm, String khfzhsdm, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String from = "from t_sys_gsjg g " +
                "  left join t_Sys_Ljkhdmys ljkhdmys on ljkhdmys.qyjgId = g.id where( g.type = 'qyjg' or g.type = 'wls') and g.deleted = 0 ";
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(qyjgQc)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("g.qc like '%" + qyjgQc + "%'");
        }
        if (StringUtils.isNotEmpty(gysfzhsdm)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("ljkhdmys.gysfzhsdm like '%" + gysfzhsdm + "%'");
        }
        if (StringUtils.isNotEmpty(khfzhsdm)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("ljkhdmys.khfzhsdm like '%" + khfzhsdm + "%'");
        }
        String countSql = "select count(1) " + from + (where.length() != 0 ? " and " : "") + where;
        String sql = "select ljkhdmys.id  as \"ljkmdmys.id\",ljkhdmys.gysfzhsdm as \"ljkmdmys.gysfzhsdm\",ljkhdmys.khfzhsdm as \"ljkmdmys.khfzhsdm\"," +
                "g.id as \"ljkmdmys.qyjgId\",g.type as \"ljkmdmys.type\" ,g.qc as ljkmdmys_qyjgQc " + from + (where.length() != 0 ? " and " : "") + where;
        SQLQuery countSqlQuery = getSession().createSQLQuery(countSql);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setTotalCount(((BigDecimal) countSqlQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        return page;
    }

    public Map getByID(Long id) {
        String sql = "select ljkmdmys.id as \"ljkmdmys.id\",ljkmdmys.nbgsId as \"ljkmdmys.nbgsId\",ljkmdmys.qyjgId as \"ljkmdmys.qyjgId\"," +
                "ljkmdmys.lx as \"ljkmdmys.lx\",gsjg.qc as \"nbgsQc\",qyjg.qc as \"qyjgQc\" " +
                "from t_sys_ljkhdmys ljkmdmys left join t_sys_gsjg gsjg on(ljkmdmys.nbgsId = gsjg.id) " +
                "left join t_sys_gsjg qyjg on(ljkmdmys.qyjgId = qyjg.id) where ljkmdmys.id = :id";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setParameter("id", id).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return (Map) sqlQuery.uniqueResult();
    }

    /**
     * 获取供应商或客户辅助核算代码
     *
     * @param gsId
     * @return
     */
    public List<Map> findKmdm(Long gsId, Integer type) {
        String sql = null;
        //供应商
        if (type == 0)
            sql = "select ys.gysfzhsdm||'@'||qyjg.qc||'@'||qyjg.id||'@'||qyjg.type as \"kmdm\",qyjg.qc as \"qyqc\" from t_sys_ljkhdmys ys  " +
                    " left join t_sys_gsjg qyjg " +
                    " on ys.qyjgid=qyjg.id where qyjg.type = \'qyjg\' and qyjg.deleted=0 and length(rtrim(ltrim(ys.gysfzhsdm)))!=0 " +
                    " union all " +
                    " select khh.gysfzhsdm||'@'||khh.qc as \"kmdm\",khh.qc as \"khhqc\" " +
                    " from t_sys_khh khh where deleted = 0 and khh.nbgs_id = ? and length(rtrim(ltrim(khh.gysfzhsdm)))!=0 "+
                    " union all " +
                    " select ys.gyskmdm||'@'||gsjg.qc||'@'||gsjg.id||'@'||gsjg.type as \"kmdm\",gsjg.qc as \"qyqc\" from t_sys_nbgskmdmys ys   " +
                    " left join t_sys_gsjg gsjg  " +
                    " on ys.xyid=gsjg.id and syid = ? where gsjg.deleted=0 and length(rtrim(ltrim(ys.gyskmdm)))!=0 ";
        //客户
        if (type == 1)
            sql = "select ys.khfzhsdm||'@'||qyjg.qc||'@'||qyjg.id||'@'||qyjg.type as \"kmdm\",qyjg.qc as \"qyqc\" from t_sys_ljkhdmys ys  " +
                    " left join t_sys_gsjg qyjg " +
                    " on ys.qyjgid=qyjg.id where qyjg.type = \'qyjg\' and qyjg.deleted=0 and length(rtrim(ltrim(ys.khfzhsdm)))!=0 " +
                    " union all " +
                    " select khh.khfzhsdm||'@'||khh.qc as \"kmdm\",khh.qc as \"khhqc\" " +
                    " from t_sys_khh khh where deleted = 0  and khh.nbgs_id = ? and length(rtrim(ltrim(khh.khfzhsdm)))!=0 "+
                   " union all " +
                   " select ys.khkmdm||'@'||gsjg.qc||'@'||gsjg.id||'@'||gsjg.type as \"kmdm\",gsjg.qc as \"qyqc\" from t_sys_nbgskmdmys ys   " +
                   " left join t_sys_gsjg gsjg  " +
                   " on ys.xyid=gsjg.id and syid = ? where gsjg.deleted=0 and length(rtrim(ltrim(ys.khkmdm)))!=0 ";
        List<Map> result = createSQLQuery(sql,gsId,gsId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    public Ljkmdmys findKmdmByGsjgId( Long qyjgId) {
        String hql = "from Ljkmdmys where   qyjgId = ?  ";
        return find(hql,  qyjgId).size() > 0 ? (Ljkmdmys) find(hql,  qyjgId).get(0) : new Ljkmdmys();
    }
}
